package com.candy.databasedocuments.service.database;

import com.candy.databasedocuments.entity.DatabaseConfig;
import com.candy.databasedocuments.entity.Table;
import com.candy.databasedocuments.entity.TableFieldInfo;
import com.candy.databasedocuments.service.AbstractDatabaseConnect;
import com.candy.databasedocuments.utils.JdbcConnectUtil;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author suYanLong
 * @version 1.0
 * @date 2023/7/5 14:44
 * @Description oracle链接处理
 */
@Service("oracle")
public class OracleDatabaseConnect extends AbstractDatabaseConnect {

    @Resource
    OracleTableMateSqlServiceImpl tableMateSqlService;

    @Override
    public void getConnectInfo(DatabaseConfig databaseConfig) {
        String url = "jdbc:oracle:thin:@" + databaseConfig.getDbAttr().getUrl() + databaseConfig.getDbAttr().getDbName();
        databaseConfig.getDbAttr().setUrl(url);
    }

    @Override
    public List<Table> getTableBaseInfoList(Connection connection) {
        //获取数据库名称
        String dateMateName = getDateMateName(connection);

        //获取数据库表名和备注
        List<Table> list = getDateMateTableName(connection, dateMateName);

        //获取表详细信息
        List<Table> tables = selectTablesFields(connection, list);

        List<Table> tablePriCols = selectTableColPri(connection, list);

        //设置对象表的备注
        for (Table table : tables) {
            for (Table tableNameNote : list) {
                if (tableNameNote.getTableName().equals(table.getTableName())) {
                    table.setTableRemark(tableNameNote.getTableRemark());
                    break;
                }
            }
        }

        // TODO 设置主键;
//        for (Table table : tables) {
//            for (Table tablePriCol : tablePriCols) {
//                String tableName = table.getTableName();
//                String colName = table.getColName();
//
//                String tableNameFlag = tablePriCol.getTableName();
//                String tableCol = tablePriCol.getColName();
//                if (tableName.equals(tableNameFlag) && colName.equals(tableCol)) {
//                    table.setRule("PK");
//                    break;
//                }
//            }
//        }
        return tables;
    }

    @Override
    public String getDateMateName(Connection connection) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String dateMateName = null;
        try {
            //执行sql
            String dateMateNameSql = tableMateSqlService.getDateMateName();
            //获取数据库操作对象
            statement = connection.prepareStatement(dateMateNameSql);

            resultSet = statement.executeQuery(dateMateNameSql);

            //处理查询结果集
            while (resultSet.next()) {
                //jdbc 中从1 开始
                dateMateName = resultSet.getString(1);
            }

        } catch (Exception e) {
            System.out.println("数据库异常--获取数据库名称");
            e.printStackTrace();
        }
        return dateMateName;
    }

    @Override
    public List<Table> getDateMateTableName(Connection connection, String dateMateName) {
        List<Table> list = new ArrayList<>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {

            String dateMateTableNameSql = tableMateSqlService.getDateMateTableName();

            statement = connection.prepareStatement(dateMateTableNameSql);

            resultSet = statement.executeQuery();

            //处理查询结果集
            String tableName = null;
            String tableComment = null;
            Table table = null;

            while (resultSet.next()) {
                //jdbc 中从1 开始
                tableName = resultSet.getString(1);
                tableComment = resultSet.getString(2);
                table = new Table(tableName, tableComment);
                list.add(table);
            }
        } catch (Exception e) {
            System.out.println("数据库异常--获取数据库表名");
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public List<Table> selectTablesFields(Connection connection, List<Table> tables) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            String sql = tableMateSqlService.selectTables(tables.stream().map(Table::getTableName).collect(Collectors.toList()));

            statement = connection.prepareStatement(sql);

            for (int i = 0; i < tables.size(); i++) {
                int j = i + 1;
                statement.setString(j, tables.get(i).getTableName());
            }

            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                new TableFieldInfo(resultSet.getString(2), resultSet.getString(4) + "(" + resultSet.getInt(5) + ")", resultSet.getString(6),
                        null, resultSet.getString(1), resultSet.getString(1), resultSet.getString(3));
            }
            // TODO 需要实际处理
        } catch (Exception e) {
            System.out.println("数据库异常--获取表详细信息");
            e.printStackTrace();
        }
        return tables;
    }

    public List<Table> selectTableColPri(Connection connection, List<Table> list) {
        List<Table> tables = new ArrayList<>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String sql = tableMateSqlService.selectTablePri(tables.stream().map(Table::getTableName).collect(Collectors.toList()));

            statement = connection.prepareStatement(sql);

            for (int i = 0; i < list.size(); i++) {
                int j = i + 1;
                statement.setString(j, list.get(i).getTableName());
            }

            //colName note type ifNull rule tableName tableNameNote
            String tableName = null;
            String tableCol = null;
            Table table = null;
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                tableName = resultSet.getString(1);
                tableCol = resultSet.getString(2);

// TODO                table = new Table(tableName, null, tableCol);
                tables.add(table);
            }
        } catch (Exception e) {
            System.out.println("数据库异常--主键字段详细信息");
            e.printStackTrace();
        } finally {
            JdbcConnectUtil.closeResources(connection, statement, resultSet);
        }
        return tables;
    }

}
